package DAO;

import com.mysql.jdbc.JDBC4PreparedStatement;
import entity.Found;
import utils.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class FoundDao {
    public ArrayList<Found> queryByClassify(String classify) {
        ArrayList<Found> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM found WHERE sort = ?");
            pst.setObject(1,classify);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int foundID = rs.getInt("foundID");
                String found_goods_name = rs.getString("found_goods_name");
                String time = rs.getString("time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Found found = new Found(foundID,found_goods_name,time,describe,place,sort,userID);
                arrayList.add(found);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public ArrayList<Found> queryBySearch(String key) {
        ArrayList<Found> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;
        key = '%'+key+'%';

        try {
            pst = connection.prepareStatement("SELECT * FROM found WHERE found_goods_name LIKE ? OR `describe` LIKE ?");
            pst.setObject(1,key);
            pst.setObject(2,key);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int foundID = rs.getInt("foundID");
                String found_goods_name = rs.getString("found_goods_name");
                String time = rs.getString("time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Found found = new Found(foundID,found_goods_name,time,describe,place,sort,userID);
                arrayList.add(found);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public ArrayList<Found> queryByID(String userid) {
        ArrayList<Found> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM found WHERE `userID`=?");
            pst.setObject(1,userid);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int foundID = rs.getInt("foundID");
                String found_goods_name = rs.getString("found_goods_name");
                String time = rs.getString("time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Found found = new Found(foundID,found_goods_name,time,describe,place,sort,userID);
                arrayList.add(found);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public ArrayList<Found> queryAll() {
        ArrayList<Found> arrayList = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        try {
            pst = connection.prepareStatement("SELECT * FROM found");

            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int foundID = rs.getInt("foundID");
                String found_goods_name = rs.getString("found_goods_name");
                String time = rs.getString("time");
                String describe = rs.getString("describe");
                String place = rs.getString("place");
                String sort = rs.getString("sort");
                int userID = rs.getInt("userID");

                Found found = new Found(foundID,found_goods_name,time,describe,place,sort,userID);
                arrayList.add(found);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return arrayList;
    }

    public Found queryByDescribeAndUserID(String describe,String UserID) {
        Found found = null;
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        int userID = Integer.valueOf(UserID);

        try {
            pst = connection.prepareStatement("SELECT * from `found` WHERE `describe` = ? AND `userID` = ?");
            pst.setObject(1,describe);
            pst.setObject(2,userID);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            ResultSet rs = pst.executeQuery();

            while(rs.next()) {
                int foundID = rs.getInt("foundID");
                String found_goods_name = rs.getString("found_goods_name");
                String time = rs.getString("time");
                String place = rs.getString("place");
                String sort = rs.getString("sort");

                found = new Found(foundID,found_goods_name,time,describe,place,sort,userID);
                return found;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return found;
    }

    public Found queryAddFount(String found_goods_name,String time,String describe,String place,String sort,String UserID) {
        Found found = null;
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        int userID = Integer.valueOf(UserID);

        try {
            pst = connection.prepareStatement("INSERT INTO `found` (`found_goods_name`, `time`, `describe`, `place`, `sort`, `userID`) VALUES (?, ?, ?, ?, ?, ?)");
            pst.setObject(1,found_goods_name);
            pst.setObject(2,time);
            pst.setObject(3,describe);
            pst.setObject(4,place);
            pst.setObject(5,sort);
            pst.setObject(6,userID);
            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            int rs = pst.executeUpdate();

            if (rs == 1) {
                found = new FoundDao().queryByDescribeAndUserID(describe, UserID);
            }


        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }

        return found;
    }

    public boolean deleteFound(String FoundID) {
        Found found = null;
        Connection connection = DBHelper.getConnection();
        PreparedStatement pst = null;

        int foundID = Integer.valueOf(FoundID);
        try {
            pst = connection.prepareStatement("DELETE FROM `found` WHERE `foundID`= ?");
            pst.setObject(1,foundID);

            System.out.println(((JDBC4PreparedStatement)pst).asSql());
            int rs = pst.executeUpdate();

            if (rs == 1) {
                return true;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close();
        }
        return false;
    }



    public static void main(String[] args) {
//        System.out.println(new FoundDao().queryByClassify("1"));
//        System.out.println(new FoundDao().queryAll());
        new FoundDao().deleteFound("1");
    }
}
